﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics.Contracts;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Schema;
using System.IO;
using System.Collections;
using EagleEyeCoreLibrary;

namespace Mota.EditModule
{
    public class Model
    {
        private static string schemaFileName = "EditModuleSchema.xsd";
#if DEBUG
        private static String schemaFilePath; 

        static Model()
        {
            schemaFilePath = Path.Combine(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath),
                                            schemaFileName);
        }
#endif
        public static DataTable GetDataTable(ConnectionConfigs configs, String tableName, bool Distinct = false, params String[] columns)
        {
            try
            {
                
                StringBuilder query = new StringBuilder();
                query.Append("SELECT ");
                if (columns.Length > 0)
                    query.AppendLine(String.Join(",\n", columns));
                else
                    query.Append(" * ");
                query.AppendLine();
                query.AppendFormat(" FROM {0} WITH(NOLOCK)", tableName);
                if (Distinct)
                    query.AppendFormat(" GROUP BY {0};", String.Join(",\n", columns));


                using (SqlConnection conn = new SqlConnection(configs.ConnectionString))
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query.ToString(), conn))
                {
                    try
                    {
                        conn.Open();
                        DataTable dt = new DataTable();
                        sqlDataAdapter.SelectCommand.CommandTimeout = configs.GetTimeOut();
                        sqlDataAdapter.Fill(dt);
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        ex.LogError("A1BFD9AA-2D98-4F7B-9584-4C34A3E02828");
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ex.LogError("BBAE67C3-CCC3-43C4-9878-F3735D3CB148");
                throw;
            }
        }

        public static DataTable GetDataTable(ConnectionConfigs configs, String tableName, IStatement statement, bool Distinct = false, params String[] columns)
        {
            try
            {
                StringBuilder query = new StringBuilder();
                query.Append("SELECT ");
                if (columns.Length > 0)
                    query.AppendLine(String.Join(", ", columns));
                else
                    query.Append(" * ");

                query.AppendFormat(" FROM {0} WITH(NOLOCK)\n", tableName);
                query.AppendFormat(" WHERE {0} ", statement);
                if (Distinct)
                    query.AppendFormat(" GROUP BY {0};", String.Join(",\n", columns));


                using (SqlConnection conn = new SqlConnection(configs.ConnectionString))
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query.ToString(), conn))
                {
                    try
                    {
                        conn.Open();
                        DataTable dt = new DataTable();
                        sqlDataAdapter.SelectCommand.CommandTimeout = configs.GetTimeOut();
                        sqlDataAdapter.Fill(dt);
                        return dt;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                ex.LogError("08034C09-B30F-4B9B-8BD1-B52701E8A966");
                throw;
            }
        }
        public static DataTable GetTopDataTable(ConnectionConfigs configs, String tableName, int topCount = 0, bool Distinct = false, params String[] columns)
        {
            try
            {
                StringBuilder query = new StringBuilder();
                query.AppendFormat("SELECT TOP {0} ",topCount);
                if (columns.Length > 0)
                    query.AppendLine(String.Join(",\n", columns));
                else
                    query.Append(" *\n");
                query.AppendFormat(" FROM {0} WITH(NOLOCK) ", tableName);
                if (Distinct)
                    query.AppendFormat(" GROUP BY {0};", String.Join(",\n", columns));


                using (SqlConnection conn = new SqlConnection(configs.ConnectionString))
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query.ToString(), conn))
                {
                    try
                    {
                        conn.Open();
                        DataTable dt = new DataTable();
                        sqlDataAdapter.SelectCommand.CommandTimeout = configs.GetTimeOut();
                        sqlDataAdapter.Fill(dt);
                        return dt;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                ex.LogError("9DD640CD-7BC8-4E5F-821A-C2CB36C0FB73");
                throw;
            }
        }

        public static SqlParameterCollection GetProcResult(ConnectionConfigs configs, String procedure, Parameter[] parameters)
        {
            try
            {
                using(SqlConnection conn = new SqlConnection(configs.ConnectionString))
                using (SqlCommand sqlCommand = new SqlCommand(procedure, conn) { CommandTimeout = configs.GetTimeOut() })
                {
                    conn.Open();
                    try
                    {

                        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
                        // Recieve parameters list for procedure
                        SqlCommandBuilder.DeriveParameters(sqlCommand);
                        // If proc params count equals input parameters count
                        // +1 additional @RETURN_VALUE parameter (default)
                        if (sqlCommand.Parameters.Count == parameters.Length + 1)
                        {
                            // Loop input params and assign to proc params
                            foreach (Parameter pair in parameters)
                            {
                                //TODO:take decision  what i need to do if error
                                // Convert c# null to DBNULL
                                if (pair.Value == null)
                                {   //assign null to value because WCF Serialize cause error while passing DBNULL
                                    sqlCommand.Parameters[pair.Key].Value = DBNull.Value;
                                }
                                else
                                {
                                    // Convert uniqueidentifier string to GUID
                                    // Beacause ms sql 2008 have some problems in converting String to GUID
                                    if (sqlCommand.Parameters[pair.Key].SqlDbType == SqlDbType.UniqueIdentifier)
                                    {
                                        sqlCommand.Parameters[pair.Key].Value = new Guid(pair.Value.ToString());
                                    }
                                    else
                                    {
                                        // Another params processing as usual 
                                        sqlCommand.Parameters[pair.Key].Value = pair.Value;
                                    }
                                }

                            }

                            sqlCommand.ExecuteNonQuery();
                            // If execution completed with success return par
                            if (sqlCommand.Parameters["@RETURN_VALUE"].Value.Equals(0))
                            {
                                return sqlCommand.Parameters;
                            }
                        }
                    }
                    finally
                    {
                        conn.Close();
                    }

                    return null;
                }

            }
            catch (Exception ex)
            {
                ex.LogError("E900372F-3C02-4674-9EC1-77F7F8ABCB3A");
                throw;
            }
        }

        public static ViewConfig GetViewConfig(ConnectionConfigs configs)
        {
            try
            {
                string query = "SELECT Configuration,NameOrig " +
                                "FROM AdminView.DBObjectsConfig WITH(NOLOCK) " +
                                "WHERE NameOrig = @VIEWNAME";
                using(SqlConnection conn = new SqlConnection(configs.ConnectionString))
                using (SqlCommand sqlComm = new SqlCommand(query, conn) {CommandTimeout = configs.GetTimeOut() })
                {

                    // Use sqlparameter because try to remove sql injection
                    sqlComm.Parameters.Add(new SqlParameter("@VIEWNAME", configs.ViewName));

                    ViewConfig result = new ViewConfig();
                    try
                    {
                        conn.Open();

                        using (SqlDataReader dr = sqlComm.ExecuteReader())
                        {
                            if (dr.HasRows && dr.Read())
                            {
                                result.ViewName = dr["NameOrig"].ToString();
                                result.XmlConfig = dr["Configuration"].ToString();
                            }
                            else
                                throw new Exception(Resources.GetString("View by name {0} not found.", configs.ViewName));
                        }

                        if (result.XmlConfig == String.Empty)
                            throw new Exception(Resources.GetString("Xml for view {0} not found.", configs.ViewName));
                    }
                    finally
                    {
                        conn.Close();
                    }
                    validateSchema(result.XmlConfig,configs);
                    return result;
                }

            }
            catch (Exception ex)
            {
                ex.LogError("CD123E51-3A58-437C-BCB1-591695AA0D63");
                throw;
            }
        }      
        private static void validateSchema(string xmlConfig,ConnectionConfigs configs)
        {
            try
            {

                if (File.Exists(schemaFilePath))
                {
                    XmlSchemaSet schemas = new XmlSchemaSet();
                    using (var str = new FileStream(schemaFilePath, FileMode.Open))
                    using (var reader = XmlReader.Create(str))
                    {
                        schemas.Add("", reader);

                        XDocument doc = XDocument.Parse(xmlConfig);
#if MAT
                        doc.Validate(schemas, (sender, eventArgs) =>
                        {
                            EventAppLogger.WriteAppWarning("Schema Validation"+(eventArgs as ValidationEventArgs).Message,configs.ConnectionString);
                        });
#endif
                    }
                }
            }
            catch (Exception ex)
            {
                ex.LogError("CAB804EC-C1E6-43D5-A80A-DBA9BC6F9B55");
                throw;
            }
        }
        public static IDictionary<string,object> GetObjectById(ConnectionConfigs configs,
                          String getFunctionName,String id)
        {

            string query = String.Format("SELECT {0} (@ID) as Result;",getFunctionName);
            using(SqlConnection conn = new SqlConnection(configs.ConnectionString))
            using (SqlCommand sqlComm = new SqlCommand(query, conn) { CommandTimeout = configs.GetTimeOut() })
            {

                // Use sqlparameter because try to remove sql injection
                SqlParameter IdParameter;
                if (String.IsNullOrEmpty(id))
                    IdParameter = new SqlParameter("@ID", DBNull.Value);
                else
                    IdParameter = new SqlParameter("@ID", id);
                sqlComm.Parameters.Add(IdParameter);
                XDocument document = null;
                try
                {
                    conn.Open();
                    using (SqlDataReader dr = sqlComm.ExecuteReader())
                    {
                        if (dr.HasRows && dr.Read())
                        {
                            string rslt = dr["Result"].ToString();
                            document = XDocument.Parse(rslt);
                        }
                        else
                            throw new KeyNotFoundException(Resources.GetString("{0} not found in {1}.", id, getFunctionName));
                    }
                }
                catch (SqlException ex)
                {
                    ex.LogError("8214A5B9-3737-4346-B511-2085CA9FD1C3");
                    throw;
                }
                finally
                {
                    conn.Close();
                }

                try
                {
                    // lookup for row element in the returned xml
                    var row = document.Descendants("ROW").FirstOrDefault();
                    if (row == null)
                        throw new Exception(Resources.GetString("ROW element not found in {0} by {1}.", getFunctionName, id));

                    // Parse simple elements from attributes values
                    var result = from item in row.Attributes()
                                 where item.Name.LocalName != "Id"
                                 select new { Key = item.Name.LocalName, Value = (object)item.Value };

                    // Parse complex elements from nested elements
                    if (row.HasElements && row.Elements("Complex").Any())
                    {
                        var tmp = from complex in row.Elements("Complex")
                                  where complex.HasElements
                                        && (complex.Attribute("FieldName") != null)
                                        && (complex.Element("Items") != null)
                                  select new { Key = complex.Attribute("FieldName").Value, Value = (object)complex.Element("Items").ToString() };
                        result = result.Concat(tmp);
                    }

                    return result.ToDictionary(el => el.Key, el => el.Value);
                }
                catch (Exception ex)
                {
                    ex.LogError("037FCAC4-E984-419D-BE99-8B7CB4AC014D");
                    throw;
                }
            }
        }
    }
    public static class ConnectionConfigsExtensions
    {
        public static int GetTimeOut(this ConnectionConfigs configs)
        {
            int result;
            if (int.TryParse(configs.CommandTimeout, out result))
                return result;
            else
                return 0;
        }
    }
}

